package Sdo.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import Sdo.pojo.History;

public class SqliteUtil {

	private static String Drivde = "org.sqlite.JDBC";
	protected static Connection connect = null;
	protected static Statement stmt = null;
	protected static String dbname = "s.db";
	
	private static Logger logger= Logger.getLogger(SqliteUtil.class);
	static {
		 try {
             Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
             connect=DriverManager.getConnection("jdbc:sqlite:db/"+dbname);//连接数据库s.db,不存在则创建
           
             Statement stmt=connect.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口 
     		
             //非常重要，如果不存在就新建这个表
             String sql = "create table if not exists history(id INTEGER PRIMARY KEY AUTOINCREMENT,name text,ip text,port int,"
					+ "username text,password text,pkey text,proxytype text,proxyip text,proxyport text,proxyuser text,proxypassword text)";
             
			logger.debug("建表语句"+sql);
			//stmt.executeUpdate("drop table if exists history");//判断是否有表history的存在。有则删除
			stmt.executeUpdate(sql);            //创建数据库
//              statement.executeUpdate("insert into tables values('zhou','156546')");//向数据库中插入数据
//             ResultSet rSet=statement.executeQuery("select*from tables");//搜索数据库，将搜索的放入数据集ResultSet中
//             while (rSet.next()) {            //遍历这个数据集
//                 System.out.println("姓名："+rSet.getString(1));//依次输出 也可以这样写 rSet.getString(“name”)
//                 System.out.println("密码："+rSet.getString("pwd"));
//             }
//               rSet.close();//关闭数据集
//               connection.close();//关闭数据库连接
//   			// 提交
 //   			connect.commit();
//   			// 关闭Statement
    			stmt.close();
               
               
         } catch (Exception e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
	}

	/*
	 * 判断sqlite表是否存在
	 */
	

//
//	public static void createTable() {
//		try {
//			connect.setAutoCommit(false);
//
//			stmt = connect.createStatement();
//			
//			//重建表先删除
//			//String sqldeltable="DROP TABLE history";
//			//stmt.executeUpdate(sqldeltable);
//			
//			String sql = "create table history(id INTEGER PRIMARY KEY AUTOINCREMENT,name text,ip text,port int,"
//					+ "username text,password text,pkey text,proxytype text,proxyip text,proxyport text,proxyuser text,"
//					+ "proxypassword text)";
//			logger.debug("建表语句"+sql);
//			
//			stmt.executeUpdate(sql);
//			// 提交
//			connect.commit();
//			// 关闭Statement
//			stmt.close();
//			//connect.close();
//
//		} catch (SQLException e) {
//			logger.debug("建表异常"+e.getMessage());
//			//e.printStackTrace();
//		}
//	}

	
	public void save(String table,History history){
		
		try {
			connect.setAutoCommit(false);

			stmt = connect.createStatement();

			String sql = "insert into "+table+"(name,ip,port,username,password,pkey,proxytype,proxyip,proxyport,proxyuser,proxypassword)values "
					+ "	('"+history.getName()+"','"+history.getIp()+"',"+history.getPort()
					+",'"+history.getUsername()+"','"+history.getPassword()+"','"+history.getPkey()+"'"
							+ ",'"+history.getProxyType()+"','"+history.getProxyIp()+"','"+history.getProxyPort()+"','"+history.getProxyUser()+"','"+history.getProxyPassword()+"')";
			logger.debug("save"+sql);
			// 执行
			
		
			stmt.executeUpdate(sql);
		
//			// 提交
			connect.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				logger.error("save"+e.getMessage());
			}finally {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				//connect.close();
			}

			

	}
	
	public void update(String table,History history) {
		
			try {
				connect.setAutoCommit(false);
				stmt = connect.createStatement();
			String sql ="update "+table+" set name = '"+history.getName()+"',"
					+"ip = '"+history.getIp()+"',"
					+"port = "+history.getPort()+","
					+"username = '"+history.getUsername()+"',"
					+"password = '"+history.getPassword()+"',"
					+"pkey = '"+history.getPkey()+"',"
					+"proxytype = '"+history.getProxyType()+"',"
					+"proxyip = '"+history.getProxyIp()+"',"
					+"proxyport = '"+history.getProxyPort()+"',"
					+"proxyuser = '"+history.getProxyUser()+"',"
					+"proxypassword = '"+history.getProxyPassword()+"'"
					+" where id = "+history.getId();
			
			logger.debug("update"+sql);
			stmt.executeUpdate(sql);

			connect.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				logger.error("save"+e.getMessage());
			}finally {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		 

	}

	public ResultSet queryAll(String table) throws SQLException  {
		
			connect.setAutoCommit(false);
			stmt = connect.createStatement();

			String sql = "select * from "+table;

			ResultSet result = stmt.executeQuery(sql);
			
			
//			while (result.next()) {
//				logger.debug(result.getString("id"));
//				logger.debug(result.getString("name"));
//				logger.debug(result.getString("ip"));
//				logger.debug(result.getString("password"));
//				logger.debug(result.getString("port"));
				//System.out.println(result.getString("key"));
//			}
			return result;
		 

	}

	public ResultSet queryById(String table,Integer id) throws SQLException {
		


		connect.setAutoCommit(false);
	
		stmt = connect.createStatement();

		String sql = "select * from "+table+" where id = "+id;

		ResultSet result = stmt.executeQuery(sql);
//		while (result.next()) {
//			System.out.println(result.getInt("id"));
//			System.out.println(result.getString("value"));
//		}
//
//		result.close();
//		stmt.close();
//		connect.close();
		return result;
		
		
	}



	public void del(String table,Integer id)  {
		
			try {
				connect.setAutoCommit(false);
			
			stmt = connect.createStatement();

			String sql = "delete from "+table+" where id = "+id;
			 stmt.executeUpdate(sql);


			connect.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				
				logger.error("del"+e.getMessage());
				e.printStackTrace();
			}finally {
					try {
						stmt.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						logger.error("del"+e.getMessage());
						e.printStackTrace();
					}
			}
	}
}
